﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace capaNegocios
{
    public class clsAlumno : clsPersona
    {
        public String usuario { get; set; }
        public String contrasena { get; set; }
        // Datos del Apoderado  
        public String apoderadoNombre { get; set; }
        public String apoderadoApellidoPaterno { get; set; }
        public String apoderadoApellidoMaterno { get; set; }
        public String apoderadoDireccion { get; set; }
        public String apoderadoTelefono { get; set; }
        public String apoderadoCelular { get; set; }
        public String apoderadoDni { get; set; }
        public String apoderadoParentesco { get; set; }

        public bool Agregar()
        {
            clsConexion oClsConexion = new clsConexion();
            SqlConnection objConexion = new SqlConnection();
            SqlCommand objComando = new SqlCommand();

            try
            {
                objConexion = oClsConexion.AbrirConexion();

                objComando.CommandType = CommandType.StoredProcedure;
                objComando.CommandText = "USP_I_AGREGARALUMNO";

                objComando.Parameters.Add(new SqlParameter("@NOMBRES", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APELLIDOPATERNO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APELLIDOMATERNO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@TELEFONO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@CELULAR", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@DIRECCION", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@DNI", SqlDbType.VarChar));

                objComando.Parameters.Add(new SqlParameter("@APONOMBRE", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOAPELLIDOP", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOAPELLIDOM", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOTELEFONO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOCELULAR", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APODIRECCION", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APODNI", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOPARENTESCO", SqlDbType.VarChar));

                objComando.Parameters["@NOMBRES"].Value = this.nombre;
                objComando.Parameters["@APELLIDOPATERNO"].Value = this.apellidoPaterno;
                objComando.Parameters["@APELLIDOMATERNO"].Value = this.apellidoMaterno;
                objComando.Parameters["@TELEFONO"].Value = this.telefono;
                objComando.Parameters["@CELULAR"].Value = this.celular;
                objComando.Parameters["@DIRECCION"].Value = this.direccion;
                objComando.Parameters["@DNI"].Value = this.dni;

                objComando.Parameters["@APONOMBRE"].Value = this.apoderadoNombre;
                objComando.Parameters["@APOAPELLIDOP"].Value = this.apoderadoApellidoPaterno;
                objComando.Parameters["@APOAPELLIDOM"].Value = this.apoderadoApellidoMaterno;
                objComando.Parameters["@APOTELEFONO"].Value = this.apoderadoTelefono;
                objComando.Parameters["@APOCELULAR"].Value = this.apoderadoCelular;
                objComando.Parameters["@APODIRECCION"].Value = this.apoderadoDireccion;
                objComando.Parameters["@APODNI"].Value = this.apoderadoDni;
                objComando.Parameters["@APOPARENTESCO"].Value = this.apoderadoParentesco;

                objComando.Connection = objConexion;
                objComando.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
            finally
            {
                objConexion.Close();
            }
        }

        public bool Actualizar()
        {
            clsConexion oClsConexion = new clsConexion();
            SqlConnection objConexion = new SqlConnection();
            SqlCommand objComando = new SqlCommand();

            try
            {
                objConexion = oClsConexion.AbrirConexion();

                objComando.CommandType = CommandType.StoredProcedure;
                objComando.CommandText = "USP_U_MODIFICARALUMNO";
                
                objComando.Parameters.Add(new SqlParameter("@ID", SqlDbType.Char));
                objComando.Parameters.Add(new SqlParameter("@NOMBRES", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APELLIDOPATERNO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APELLIDOMATERNO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@TELEFONO", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@CELULAR", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@DIRECCION", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@DNI", SqlDbType.Char));
                objComando.Parameters.Add(new SqlParameter("@ESTADO", SqlDbType.Char));
                objComando.Parameters.Add(new SqlParameter("@APONOMBRE", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOAPELLIDOP", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOAPELLIDOM", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APOCELULAR", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APODIRECCION", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APODNI", SqlDbType.Char));
                objComando.Parameters.Add(new SqlParameter("@APOPARENTESCO", SqlDbType.Char));

                objComando.Parameters["@ID"].Value = this.id;
                objComando.Parameters["@NOMBRES"].Value = this.nombre;
                objComando.Parameters["@APELLIDOPATERNO"].Value = this.apellidoPaterno;
                objComando.Parameters["@APELLIDOMATERNO"].Value = this.apellidoMaterno;
                objComando.Parameters["@TELEFONO"].Value = this.telefono;
                objComando.Parameters["@CELULAR"].Value = this.celular;
                objComando.Parameters["@DIRECCION"].Value = this.direccion;
                objComando.Parameters["@DNI"].Value = this.dni;
                objComando.Parameters["@ESTADO"].Value = this.estado;
                objComando.Parameters["@APONOMBRE"].Value = this.apoderadoNombre;
                objComando.Parameters["@APOAPELLIDOP"].Value = this.apoderadoApellidoPaterno;
                objComando.Parameters["@APOAPELLIDOM"].Value = this.apoderadoApellidoMaterno;
                objComando.Parameters["@APOCELULAR"].Value = this.apoderadoCelular;
                objComando.Parameters["@APODIRECCION"].Value = this.apoderadoDireccion;
                objComando.Parameters["@APODNI"].Value = this.apoderadoDni;
                objComando.Parameters["@APOPARENTESCO"].Value = this.apoderadoParentesco;

                objComando.Connection = objConexion;
                objComando.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
            finally
            {
                objConexion.Close();
            }
        }

        public DataTable Buscar()
        {
            clsConexion oClsConexion = new clsConexion();
            SqlConnection objConexion = new SqlConnection();
            SqlCommand objComando = new SqlCommand();

            try
            {
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                DataTable objDataTable = new DataTable();

                objConexion = oClsConexion.AbrirConexion();

                objComando.CommandType = CommandType.StoredProcedure;                              
                objComando.CommandText = "USP_S_BUSCARALUMNO";          

                objComando.Parameters.Add(new SqlParameter("@APELLIDOP", SqlDbType.VarChar));
                objComando.Parameters.Add(new SqlParameter("@APELLIDOM", SqlDbType.VarChar));
                
                objComando.Parameters["@APELLIDOP"].Value = this.apellidoPaterno;
                objComando.Parameters["@APELLIDOM"].Value = this.apellidoMaterno;       

                objComando.Connection = objConexion;
                objComando.ExecuteNonQuery();

                objDataAdapter.SelectCommand = objComando;
                objDataAdapter.Fill(objDataTable);

                return objDataTable;
            }
            catch (SqlException ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                objConexion.Close();
            }
        }

        public DataTable BuscarPorCodigo()
        {
            clsConexion oClsConexion = new clsConexion();
            SqlConnection objConexion = new SqlConnection();
            SqlCommand objComando = new SqlCommand();

            try
            {
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                DataTable objDataTable = new DataTable();

                objConexion = oClsConexion.AbrirConexion();

                objComando.CommandType = CommandType.StoredProcedure;
                objComando.CommandText = "USP_S_BUSCARALUMNOCODIGO";

                objComando.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar));       

                objComando.Parameters["@ID"].Value = this.id;    

                objComando.Connection = objConexion;
                objComando.ExecuteNonQuery();

                objDataAdapter.SelectCommand = objComando;
                objDataAdapter.Fill(objDataTable);

                return objDataTable;
            }
            catch (SqlException ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                objConexion.Close();
            }
        }    
    }    
}
